﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Internet_Deliver_Therapy.Models;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Security;

namespace Internet_Deliver_Therapy.Controllers
{
    public class UsersController
    {
       
        //Create user
        public bool createUsers(Users user)
        {
            string password = "";
            string psychiatrist = "";
            string homeNo = "";
            string designation = "";
            string officeNo = "";
            string query = "";
            if (user.AccountType.Equals("Psychologist") || user.AccountType.Equals("Psychiatrist"))
            {
                Random rnd = new Random();
                int numOfAlphaNumeric = rnd.Next(1,6);
                password = Membership.GeneratePassword(6, numOfAlphaNumeric);
                designation = user.Designation.ToString();
                officeNo = user.OfficeNo.ToString();
            }
            else
            {
                password = user.Password.ToString();
                psychiatrist = user.Psychiatrist.ToString();
                homeNo = user.HomeNo.ToString();
            }
            bool created = true;
    
            string nric = user.Nric.ToString();
            string firstName = user.FirstName.ToString();
            string lastName = user.LastName.ToString();
            string race = user.Race.ToString();
            string email = user.Email.ToString();
            string accountType = user.AccountType.ToString();
            string notifyVia = user.NotifyVia.ToString();
            string mobileNo = user.MobileNo.ToString();
            string gender = user.Gender.ToString();
            string status = user.Status.ToString();
            string newsletter = user.Newsletter.ToString();
            string profilePictureExist = user.ProfilePictureExist.ToString();
            DateTime dob = user.Dob;
            DateTime employmentDate = user.EmploymentDate;

            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);

            myconn.Open();
            SqlCommand cmd = new SqlCommand();

            cmd.Connection = myconn;

            if (user.AccountType.Equals("Psychologist") || user.AccountType.Equals("Psychiatrist"))
            {
                query = "INSERT INTO Users (NRIC, FirstName, LastName, Race, Email, Password, AccountType,"
                    + " Designation, NotifyVia, MobileNo, OfficeNo, Gender, Status, ProfilePictureExist, DOB, EmploymentDate) "
                    + "VALUES (@NRIC, @FirstName, @LastName, @Race, @Email, @Password, @AccountType,"
                    + " @Designation, @NotifyVia, @MobileNo, @OfficeNo, @Gender, @Status, @ProfilePictureExist, @DOB, @EmploymentDate)";

                /* Do not insert value into query directly, there will be datetime problem because datetime will be converted into string.
                 * Use AddWithValue(below)
                 */
                cmd.Parameters.AddWithValue("@NRIC", nric);
                cmd.Parameters.AddWithValue("@FirstName", firstName);
                cmd.Parameters.AddWithValue("@LastName", lastName);
                cmd.Parameters.AddWithValue("@Race", race);
                cmd.Parameters.AddWithValue("@Email", email);
                cmd.Parameters.AddWithValue("@Password", password);
                cmd.Parameters.AddWithValue("@AccountType", accountType);
                cmd.Parameters.AddWithValue("@Designation", designation);
                cmd.Parameters.AddWithValue("@NotifyVia", notifyVia);
                cmd.Parameters.AddWithValue("@MobileNo", mobileNo);
                cmd.Parameters.AddWithValue("@OfficeNo", officeNo);
                cmd.Parameters.AddWithValue("@Gender", gender);
                cmd.Parameters.AddWithValue("@Status", status);
                cmd.Parameters.AddWithValue("@ProfilePictureExist", profilePictureExist);
                cmd.Parameters.AddWithValue("@DOB", dob);
                cmd.Parameters.AddWithValue("@EmploymentDate", employmentDate);
            }
            else
            {
                query = "INSERT INTO Users (NRIC, FirstName, LastName, Race, Email, Password, AccountType, Psychiatrist,"
                    + " NotifyVia, MobileNo, HomeNo, Gender, Status, Newsletter, ProfilePictureExist, DOB) "
                    + "VALUES (@NRIC, @FirstName, @LastName, @Race, @Email, @Password, @AccountType, @Psychiatrist,"
                    + " @NotifyVia, @MobileNo, @HomeNo, @Gender, @Status, @Newsletter, @ProfilePictureExist, @DOB)";

                /* Do not insert value into query directly, there will be datetime problem because datetime will be converted into string.
                 * Use AddWithValue(below)
                 */
                cmd.Parameters.AddWithValue("@NRIC", nric);
                cmd.Parameters.AddWithValue("@FirstName", firstName);
                cmd.Parameters.AddWithValue("@LastName", lastName);
                cmd.Parameters.AddWithValue("@Race", race);
                cmd.Parameters.AddWithValue("@Email", email);
                cmd.Parameters.AddWithValue("@Password", password);
                cmd.Parameters.AddWithValue("@AccountType", accountType);
                cmd.Parameters.AddWithValue("@Psychiatrist", psychiatrist);
                cmd.Parameters.AddWithValue("@NotifyVia", notifyVia);
                cmd.Parameters.AddWithValue("@MobileNo", mobileNo);
                cmd.Parameters.AddWithValue("@HomeNo", homeNo);
                cmd.Parameters.AddWithValue("@Gender", gender);
                cmd.Parameters.AddWithValue("@Status", status);
                cmd.Parameters.AddWithValue("@Newsletter", newsletter);
                cmd.Parameters.AddWithValue("@ProfilePictureExist", profilePictureExist);
                cmd.Parameters.AddWithValue("@DOB", dob);
            }

            cmd.CommandText = query;

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ex.ToString();
                created = false;
            }
            finally
            {
                myconn.Close();
            }

            return created;
        }

        //Assist in user login
        public string login(string email, string password)
        {
            string accountType = "";
            string nric = "";

            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);
            myconn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = myconn;
            String query = "SELECT NRIC, AccountType FROM Users WHERE Email = @Email AND Password = @Password;";

            cmd.Parameters.AddWithValue("@Email", email);
            cmd.Parameters.AddWithValue("@Password", password);

            cmd.CommandText = query;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        nric = reader.GetString(0);
                        accountType = reader.GetString(1);
                    }
                }

            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                myconn.Close();
                if(!accountType.Equals(""))
                {
                    logLogin(nric);
                }
            }

            return accountType;
        }

        //Gets user's first name to display upon login
        public string getUserFirstName(string email)
        {
            string firstName = "";

            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);
            myconn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = myconn;
            String query = "SELECT FirstName FROM Users WHERE Email = @Email;";

            cmd.Parameters.AddWithValue("@Email", email);

            cmd.CommandText = query;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        firstName = reader.GetString(0);
                    }
                }

            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                myconn.Close();
            }

            return firstName;
        }

        //Gets user's last login's ID
        public int getLoginID(string nric)
        {
            int id = 0;

            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);
            myconn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = myconn;
            String query = "SELECT MAX(ID) AS MaxID FROM UsersLogin WHERE NRIC = @NRIC;";

            cmd.Parameters.AddWithValue("@NRIC", nric);

            cmd.CommandText = query;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        id = reader.GetInt32(0);
                    }
                }

            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                myconn.Close();
            }

            return id;
        }
        
        //Logs user's current login
        public void logLogin(string email)
        {
            int id = getLoginID(email);
            
            DateTime currentDateTime = DateTime.Now;
       
            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);

            myconn.Open();
            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = myconn;

            String query = "INSERT INTO UsersLogin (ID, NRIC, DateTime)"
                + "VALUES (@ID, @NRIC, @DateTime)";

            cmd.Parameters.AddWithValue("@ID", id+1);
            cmd.Parameters.AddWithValue("@NRIC", email);
            cmd.Parameters.AddWithValue("@DateTime", currentDateTime);

            cmd.CommandText = query;

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                myconn.Close();
            }
        }

        //Gets user's last login
        public string getLoginUserLastLogin(string nric)
        {
            int currentId = getLoginID(nric);
            int lastId = currentId - 1;
            string lastLogin = "";

            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);
            myconn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = myconn;
            String query = "SELECT DateTime FROM UsersLogin WHERE NRIC = @NRIC AND ID = @ID;";

            cmd.Parameters.AddWithValue("@NRIC", nric);
            cmd.Parameters.AddWithValue("@ID", lastId);

            cmd.CommandText = query;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        lastLogin = reader.GetDateTime(0).ToString();
                    }
                }

            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                myconn.Close();
            }

            return lastLogin;
        }

        //Get user object using user's email
        public Users getUserByEmail(string email)
        {
            Users user = null;

            string nric = "";
            string firstName = "";
            string lastName = "";
            string race = "";
            string accountType = "";
            string psychiatrist = "";
            string psychologist = "";
            string designation = "";
            string notifyVia = "";
            string mobileNo = "";
            string homeNo = "";
            string officeNo = "";

            char gender = '0';
            char status = '0';
            char newsletter = '0';
            char profilePictureExist = '0';

            DateTime dob = new DateTime();
            DateTime employmentDate = new DateTime();

            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);
            myconn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = myconn;
            String query = "SELECT NRIC, FirstName, LastName, Race, AccountType, Psychiatrist, Psychologist, Designation, NotifyVia, "
                + "MobileNo, HomeNo, OfficeNo, Gender, Status, Newsletter, ProfilePictureExist, DOB, EmploymentDate FROM Users "
                + "WHERE Email = @Email;";

            cmd.Parameters.AddWithValue("@Email", email);

            cmd.CommandText = query;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        nric = reader.GetString(0);
                        firstName = reader.GetString(1);
                        lastName = reader.GetString(2);
                        race = reader.GetString(3);
                        accountType = reader.GetString(4);
                        psychiatrist = reader.GetString(5);
                        psychologist = reader.GetString(6);
                        designation = reader.GetString(7);
                        notifyVia = reader.GetString(8);
                        mobileNo = reader.GetString(9);
                        homeNo = reader.GetString(10);
                        officeNo = reader.GetString(11);
                        string gender_string = reader.GetString(12);
                        string status_string = reader.GetString(13);
                        string newsletter_string = reader.GetString(14);
                        string profilePictureExist_string = reader.GetString(15);
                        string dob_string = reader.GetString(16);
                        string employmentDate_string = reader.GetString(17);

                        gender = gender_string[0];
                        status = status_string[0];
                        newsletter = newsletter_string[0];
                        profilePictureExist = profilePictureExist_string[0];
                        dob = Convert.ToDateTime(dob_string);
                        employmentDate = Convert.ToDateTime(employmentDate_string);
                    }
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                myconn.Close();

                string lastLogin_string = getLoginUserLastLogin(nric);
                DateTime lastLogin = new DateTime();
                if (!lastLogin_string.Equals(""))
                {
                    lastLogin = Convert.ToDateTime(lastLogin_string);
                }
                
                user = new Users(nric, firstName, lastName, race, email, accountType, psychiatrist, psychologist, designation,
                    notifyVia, mobileNo, homeNo, officeNo, gender, status, newsletter, profilePictureExist, dob, lastLogin, employmentDate);
            }

            return user;
        }

        //Get user object using user's NRIC
        public Users getUserByNRIC(string nric)
        {
            Users user = null;

            string email = "";
            string firstName = "";
            string lastName = "";
            string race = "";
            string accountType = "";
            string psychiatrist = "";
            string psychologist = "";
            string designation = "";
            string notifyVia = "";
            string mobileNo = "";
            string homeNo = "";
            string officeNo = "";
            //To be removed
            string password = "";

            char gender = '0';
            char status = '0';
            char newsletter = '0';
            char profilePictureExist = '0';

            DateTime dob = new DateTime();
            DateTime lastlogin = new DateTime();
            DateTime employmentDate = new DateTime();

            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);
            myconn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = myconn;
            /*
            String query = "SELECT FirstName, LastName, Race, Email, AccountType, Psychiatrist, Psychologist, Designation, NotifyVia, "
                + "MobileNo, HomeNo, OfficeNo, Gender, Status, Newsletter, ProfilePictureExist, DOB, EmploymentDate FROM Users "
                + "WHERE NRIC = @NRIC;";
            */

            //To be replaced with above
            String query = "SELECT FirstName, LastName, Race, Email, AccountType, Psychiatrist, Psychologist, Designation, NotifyVia, "
                + "MobileNo, HomeNo, OfficeNo, Gender, Status, Newsletter, ProfilePictureExist, DOB, EmploymentDate, Password FROM Users "
                + "WHERE NRIC = @NRIC;";

            cmd.Parameters.AddWithValue("@NRIC", nric);

            cmd.CommandText = query;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        firstName = reader.GetString(0);
                        lastName = reader.GetString(1);
                        race = reader.GetString(2);
                        email = reader.GetString(3);
                        accountType = reader.GetString(4);
                        psychiatrist = reader.GetString(5);
                        psychologist = reader.GetString(6);
                        designation = reader.GetString(7);
                        notifyVia = reader.GetString(8);
                        mobileNo = reader.GetString(9);
                        homeNo = reader.GetString(10);
                        officeNo = reader.GetString(11);
                        string gender_string = reader.GetString(12);
                        string status_string = reader.GetString(13);
                        string newsletter_string = reader.GetString(14);
                        string profilePictureExist_string = reader.GetString(15);
                        //string dob_string = reader.GetString(16);
                        //string employmentDate_string = reader.GetString(17);
                        dob = reader.GetDateTime(16);
                        employmentDate = reader.GetDateTime(17);

                        //To be removed
                        password = reader.GetString(18);

                        gender = gender_string[0];
                        status = status_string[0];
                        newsletter = newsletter_string[0];
                        profilePictureExist = profilePictureExist_string[0];
                        //dob = Convert.ToDateTime(dob_string);
                        //employmentDate = Convert.ToDateTime(employmentDate_string);

                    }
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                myconn.Close();

                string lastLogin_string = getLoginUserLastLogin(nric);
                DateTime lastLogin = new DateTime();
                if (!lastLogin_string.Equals(""))
                {
                    lastLogin = Convert.ToDateTime(lastLogin_string);
                }

                user = new Users(nric, firstName, lastName, race, email, accountType, psychiatrist, psychologist, designation,
                    notifyVia, mobileNo, homeNo, officeNo, gender, status, newsletter, profilePictureExist, dob, lastlogin, employmentDate);

                user.Password = password;
            }

            return user;
        }

        //Gets user's first name to display upon login
        public char getUserCurrentStatus(string nric)
        {
            string currentStatus_string = "";
            char currentStatus = '0';

            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);
            myconn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = myconn;
            String query = "SELECT Status FROM Users WHERE NRIC = @NRIC;";

            cmd.Parameters.AddWithValue("@NRIC", nric);

            cmd.CommandText = query;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        currentStatus_string = reader.GetString(0);
                    }
                }

            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                myconn.Close();
                currentStatus = currentStatus_string[0];
            }

            return currentStatus;
        }

        //Activate/Deactivate user's account
        public bool activateDeactivateAccount(string nric)
        {
            bool updated = true;

            char accountStatus = getUserCurrentStatus(nric);
            char newStatus = 'F';

            if (accountStatus.Equals('F'))
            {
                newStatus = 'T';
            }

            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);

            myconn.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = myconn;

            String query = "UPDATE Users SET Status = @Status WHERE NRIC = @NRIC;";

            cmd.Parameters.AddWithValue("@Status", newStatus);
            cmd.Parameters.AddWithValue("@NRIC", nric);
            cmd.CommandText = query;

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ex.ToString();
                updated = false;
            }
            finally
            {
                myconn.Close();
            }

            return updated;
        }

        //Check if email address already exist
        public string checkEmail(string email)
        {
            string nric = "";
            SqlConnection myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["IDTConnection"].ConnectionString);
            myconn.Open();
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            cmd.Connection = myconn;
            String query = "SELECT NRIC FROM Users WHERE Email = @Email;";

            cmd.Parameters.AddWithValue("@Email", email);

            cmd.CommandText = query;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        nric = reader.GetString(0);
                    }
                }
            }
            catch (Exception ex)
            {
                ex.ToString();
            }
            finally
            {
                myconn.Close();
            }

            return nric;
        }
    }
}